from backend.utils.db import get_db_connection


class ProductService:
    # 产品相关操作
    def get_all_products(self):
        """获取所有产品信息"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT product_id, 
                             name, 
                             price, 
                             stock, 
                             category, 
                             series,
                             description, 
                             created_at
                      FROM products 
                      """
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(f"获取产品列表失败: {e}")
            return []

    def get_product_details(self, product_id):
        """获取产品完整详情（整合产品和知识库信息）"""
        try:
            with get_db_connection() as (cursor, conn):
                # 获取产品基本信息
                cursor.execute("""
                               SELECT p.*, pk.ingredients, pk.usage_instructions, pk.effect, pk.skin_type
                               FROM products p
                                        LEFT JOIN product_knowledge pk ON p.product_id = pk.product_id
                               WHERE p.product_id = %s
                               """, (product_id,))
                product = cursor.fetchone()

                if product:
                    # 设置默认值
                    product['ingredients'] = product['ingredients'] or "暂无成分信息"
                    product['usage_instructions'] = product['usage_instructions'] or "暂无使用方法"
                    product['effect'] = product['effect'] or "暂无功效信息"
                    product['skin_type'] = product['skin_type'] or "暂无肤质信息"

                return product
        except Exception as e:
            print(f"获取产品详情失败: {e}")
            return None

    def fuzzy_search_products(self, name):
        """增强的模糊产品搜索"""
        try:
            with get_db_connection() as (cursor, conn):
                # 使用更宽松的匹配
                sql = """
                      SELECT p.product_id, p.name, p.price, p.series
                      FROM products p
                      WHERE p.name LIKE %s
                         OR p.series LIKE %s
                         OR p.category LIKE %s LIMIT 5 \
                      """
                search_term = f"%{name}%"
                cursor.execute(sql, (search_term, search_term, search_term))
                return cursor.fetchall()
        except Exception as e:
            print(f"产品搜索失败: {e}")
            return []

    def get_products_by_series(self, series_name):
        """获取指定系列的所有产品"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT product_id, name, price
                      FROM products
                      WHERE series LIKE %s \
                      """
                cursor.execute(sql, (f"%{series_name}%",))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取系列产品失败: {e}")
            return []

    def recommend_products(self, keyword):
        """推荐相关产品"""
        # 确定推荐类型
        if keyword == "popular":
            # 返回热门产品
            return self.get_popular_products()

        # 按关键词推荐
        return self.fuzzy_search_products(keyword)

    def get_popular_products(self, limit=5):
        """获取热门产品"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT p.product_id, p.name, p.price
                      FROM products p
                      ORDER BY p.sales DESC
                          LIMIT %s \
                      """
                cursor.execute(sql, (limit,))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取热门产品失败: {e}")
            return []

    def get_products_by_category(self, category):
        """按类别获取产品"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT product_id, name, price, stock
                      FROM products
                      WHERE category LIKE %s LIMIT 5 \
                      """
                cursor.execute(sql, (f"%{category}%",))
                return cursor.fetchall()
        except Exception as e:
            print(f"按类别获取产品失败: {e}")
            return []

    def recommend_products(self, keyword):
        """推荐相关产品"""
        if keyword == "all":
            # 返回所有产品中的热门产品
            return self.get_products_by_category("精华")[:3]  # 示例

        # 按关键词推荐
        return self.enhanced_fuzzy_search(keyword)

    def get_product_by_id(self, product_id):
        """根据产品ID获取产品详情"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT p.product_id,
                             p.name,
                             p.price,
                             p.stock,
                             p.category,
                             p.series,
                             pk.ingredients,
                             pk.usage_instructions,
                             pk.effect,
                             pk.skin_type
                      FROM products p
                               LEFT JOIN product_knowledge pk ON p.product_id = pk.product_id
                      WHERE p.product_id = %s \
                      """
                cursor.execute(sql, (product_id,))
                result = cursor.fetchone()

                # 确保所有字段都有值
                if result:
                    # 设置默认值
                    result['effect'] = result['effect'] or '暂无描述'
                    result['skin_type'] = result['skin_type'] or '暂无描述'
                    result['usage_instructions'] = result['usage_instructions'] or '暂无说明'
                    result['ingredients'] = result['ingredients'] or '暂无成分信息'

                return result
        except Exception as e:
            print(f"获取产品详情失败: {str(e)}")
            return None

    def get_product_aliases(self, product_id=None):
        """获取产品别名列表"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT p.product_id, p.name AS standard_name, GROUP_CONCAT(DISTINCT s.synonym) AS aliases
                      FROM products p
                               LEFT JOIN synonyms s ON s.keyword = p.name \
                      """
                params = []

                if product_id:
                    sql += " WHERE p.product_id = %s"
                    params.append(product_id)

                sql += " GROUP BY p.product_id, p.name"

                cursor.execute(sql, tuple(params))

                if product_id:
                    return cursor.fetchone()
                else:
                    return cursor.fetchall()
        except Exception as e:
            print(f"获取产品别名失败: {e}")
            return [] if not product_id else None

    def add_product(self, product_data):
        """添加新产品"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      INSERT INTO products
                          (name, price, stock, category, series, description, created_at)
                      VALUES (%s, %s, %s, %s, %s, %s, NOW()) 
                      """
                cursor.execute(sql, (
                    product_data['name'],
                    product_data['price'],
                    product_data['stock'],
                    product_data['category'],
                    product_data['series'],
                    product_data['description']
                ))
                conn.commit()
                return cursor.lastrowid
        except Exception as e:
            conn.rollback()
            print(f"添加产品失败: {e}")
            return None

    def update_product(self, product_id, product_data):
        """更新产品信息"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      UPDATE products 
                      SET name        = %s, 
                          price       = %s, 
                          stock       = %s, 
                          category    = %s, 
                          series      = %s, 
                          description = %s
                      WHERE product_id = %s 
                      """
                cursor.execute(sql, (
                    product_data['name'],
                    product_data['price'],
                    product_data['stock'],
                    product_data['category'],
                    product_data['series'],
                    product_data['description'],
                    product_id
                ))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            conn.rollback()
            print(f"更新产品失败: {e}")
            return False

    def delete_product(self, product_id):
        """删除产品"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = "DELETE FROM products WHERE product_id = %s"
                cursor.execute(sql, (product_id,))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            conn.rollback()
            print(f"删除产品失败: {e}")
            return False

    # 产品知识相关操作
    def get_product_knowledge(self, product_id=None):
        """获取产品相关知识，可指定产品ID"""
        try:
            with get_db_connection() as (cursor, conn):
                if product_id:
                    sql = """SELECT * FROM product_knowledge WHERE product_id = %s"""
                    cursor.execute(sql, (product_id,))
                    return cursor.fetchone()
                else:
                    sql = """
                          SELECT p.product_id, 
                                 p.name, 
                                 pk.ingredients,
                                 pk.usage_instructions, 
                                 pk.effect, 
                                 pk.skin_type
                          FROM product_knowledge pk
                                   JOIN products p ON pk.product_id = p.product_id 
                          """
                    cursor.execute(sql)
                    return cursor.fetchall()
        except Exception as e:
            print(f"获取产品知识失败: {e}")
            return [] if not product_id else None

    def get_product_knowledge_by_id(self, product_id):
        """根据产品ID获取产品知识详情"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT pk.*, p.name
                      FROM product_knowledge pk
                               JOIN products p ON pk.product_id = p.product_id
                      WHERE pk.product_id = %s
                      """
                cursor.execute(sql, (product_id,))
                return cursor.fetchone()
        except Exception as e:
            print(f"获取产品知识详情失败: {e}")
            return None

    def search_products_by_skin_type(self, skin_type):
        """根据肤质类型搜索产品"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT p.*, pk.skin_type, pk.effect
                      FROM products p
                               JOIN product_knowledge pk ON p.product_id = pk.product_id
                      WHERE pk.skin_type LIKE %s
                      """
                cursor.execute(sql, (f'%{skin_type}%',))
                return cursor.fetchall()
        except Exception as e:
            print(f"根据肤质搜索产品失败: {str(e)}")
            return []

    def search_product_knowledge(self, keyword):
        """搜索产品知识"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT p.product_id, 
                             p.name, 
                             pk.ingredients,
                             pk.usage_instructions, 
                             pk.effect, 
                             pk.skin_type
                      FROM product_knowledge pk
                               JOIN products p ON pk.product_id = p.product_id
                      WHERE p.name LIKE %s 
                         OR pk.ingredients LIKE %s
                         OR pk.effect LIKE %s 
                         OR pk.skin_type LIKE %s 
                      """
                search_term = f"%{keyword}%"
                cursor.execute(sql, (search_term, search_term, search_term, search_term))
                return cursor.fetchall()
        except Exception as e:
            print(f"搜索产品知识失败: {e}")
            return []

    def add_product_knowledge(self, knowledge_data):
        """添加产品知识"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      INSERT INTO product_knowledge
                          (product_id, ingredients, usage_instructions, effect, skin_type)
                      VALUES (%s, %s, %s, %s, %s) 
                      """
                cursor.execute(sql, (
                    knowledge_data['product_id'],
                    knowledge_data['ingredients'],
                    knowledge_data['usage_instructions'],
                    knowledge_data['effect'],
                    knowledge_data['skin_type']
                ))
                conn.commit()
                return True
        except Exception as e:
            conn.rollback()
            print(f"添加产品知识失败: {e}")
            return False

    def update_product_knowledge(self, product_id, knowledge_data):
        """更新产品知识"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      UPDATE product_knowledge 
                      SET ingredients        = %s, 
                          usage_instructions = %s, 
                          effect             = %s, 
                          skin_type          = %s
                      WHERE product_id = %s 
                      """
                cursor.execute(sql, (
                    knowledge_data['ingredients'],
                    knowledge_data['usage_instructions'],
                    knowledge_data['effect'],
                    knowledge_data['skin_type'],
                    product_id
                ))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            conn.rollback()
            print(f"更新产品知识失败: {e}")
            return False

    def delete_product_knowledge(self, product_id):
        """删除产品知识"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = "DELETE FROM product_knowledge WHERE product_id = %s"
                cursor.execute(sql, (product_id,))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            conn.rollback()
            print(f"删除产品知识失败: {e}")
            return False